/*
 * Copyright (C) 2009-2012 Geometer Plus <contact@geometerplus.com>
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301, USA.
 */

package com.example.administrator.fangbook.read.library.db;


import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;


import org.geometerplus.fbreader.book.FileInfo;
import org.geometerplus.fbreader.book.MTxtBook;
import org.geometerplus.fbreader.book.ReadBook;
import org.geometerplus.fbreader.filetype.FileTypeTxt;
import org.geometerplus.zlibrary.core.filesystem.ZLFile;
import org.geometerplus.zlibrary.core.filesystem.ZLTxtFile;

import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;

public final class SQLiteBooksDatabase extends BooksDatabase {

    private SQLiteDatabase myDatabase;
    private SQLiteStatement myRemoveFileInfoStatement;
    private SQLiteStatement myInsertFileInfoStatement;
    private SQLiteStatement myUpdateFileInfoStatement;


    public SQLiteBooksDatabase(Context context, String instanceId) {
        myDatabase = context.openOrCreateDatabase("books_htl.db", Context.MODE_PRIVATE, null);
        migrate(context);
    }

    private void migrate(Context context) {
        final int version = myDatabase.getVersion();
        final int currentVersion = 1;
        if (version >= currentVersion) {
            return;
        }
        myDatabase.beginTransaction();
        switch (version) {
            case 0:
                createTables();
//            case 1:
//                updateTables1();
//            case 2:
//                updateTables2();
//            case 3:
//                updateTables3();
//            case 4:
//                updateTables4();
//            case 5:
//                updateTables5();
//            case 6:
//                updateTables6();
//            case 7:
//                updateTables7();
//            case 8:
//                updateTables8();
//            case 9:
//                updateTables9();
//            case 10:
//                updateTables10();
//            case 11:
//                updateTables11();
//            case 12:
//                updateTables12();
//            case 13:
//                updateTables13();
//            case 14:
//                updateTables14();
//            case 15:
//                updateTables15();
//            case 16:
//                updateTables16();
//            case 17:
//                updateTables17();
//            case 18:
//                updateTables18();
//            case 19:
//                updateTables19();
        }
        myDatabase.setTransactionSuccessful();
        myDatabase.endTransaction();
        myDatabase.execSQL("VACUUM");
        myDatabase.setVersion(currentVersion);
    }

    /**
     * 创建表
     */
    private void createTables() {
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS Books(" +
                        "book_id INTEGER PRIMARY KEY," +
                        "encoding TEXT," +
                        "language TEXT," +
                        "title TEXT NOT NULL," +
                        "file_name TEXT UNIQUE NOT NULL," +
                        "file_mode TEXT)");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS Authors(" +
                        "author_id INTEGER PRIMARY KEY," +
                        "name TEXT NOT NULL," +
                        "sort_key TEXT NOT NULL," +
                        "CONSTRAINT Authors_Unique UNIQUE (name, sort_key))");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS BookAuthor(" +
                        "author_id INTEGER NOT NULL REFERENCES Authors(author_id)," +
                        "book_id INTEGER NOT NULL REFERENCES Books(book_id)," +
                        "author_index INTEGER NOT NULL," +
                        "CONSTRAINT BookAuthor_Unique0 UNIQUE (author_id, book_id)," +
                        "CONSTRAINT BookAuthor_Unique1 UNIQUE (book_id, author_index))");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS Series(" +
                        "series_id INTEGER PRIMARY KEY," +
                        "name TEXT UNIQUE NOT NULL)");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS BookSeries(" +
                        "series_id INTEGER NOT NULL REFERENCES Series(series_id)," +
                        "book_id INTEGER NOT NULL UNIQUE REFERENCES Books(book_id)," +
                        "book_index INTEGER)");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS Tags(" +
                        "tag_id INTEGER PRIMARY KEY," +
                        "name TEXT NOT NULL," +
                        "parent INTEGER REFERENCES Tags(tag_id)," +
                        "CONSTRAINT Tags_Unique UNIQUE (name, parent))");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS BookTag(" +
                        "tag_id INTEGER REFERENCES Tags(tag_id)," +
                        "book_id INTEGER REFERENCES Books(book_id)," +
                        "CONSTRAINT BookTag_Unique UNIQUE (tag_id, book_id))");
        myDatabase.execSQL(
                "CREATE TABLE IF NOT EXISTS Files(" +
                        "file_id INTEGER PRIMARY KEY," +
                        "name TEXT NOT NULL," +
                        "parent_id INTEGER REFERENCES Files(file_id)," +
                        "size INTEGER," +
                        "CONSTRAINT Files_Unique UNIQUE (name, parent_id))");
    }

    /**
     * 将数据库的信息封装到FileInfo里面
     *
     * @param file
     * @return
     */
    @Override
    public Collection<FileInfo> loadFileInfos(ZLFile file) {
        final LinkedList<ZLFile> fileStack = new LinkedList<ZLFile>();
        for (; file != null; file = file.getParent()) {
            fileStack.addFirst(file);//将file的单链表转换linked链表
        }
        final ArrayList<FileInfo> infos = new ArrayList<FileInfo>(fileStack.size());
        final String[] parameters = {null};
        FileInfo current = null;
        try {
            for (ZLFile f : fileStack) {
                parameters[0] = f != null ? f.getLongName() : "";
                if (parameters[0] == null) {
                    parameters[0] = "";
                }
                final Cursor cursor = myDatabase.rawQuery(
                        (current == null) ?
                                "SELECT file_id,size FROM Files WHERE name = ?" :
                                "SELECT file_id,size FROM Files WHERE parent_id = " + current.Id + " AND name = ?",
                        parameters
                );
                if (cursor.moveToNext()) {
                    current = createFileInfo(cursor.getLong(0), parameters[0], current);
                    if (!cursor.isNull(1)) {
                        current.FileSize = cursor.getLong(1);
                    }
                    infos.add(current);
                    cursor.close();
                } else {
                    cursor.close();
                    break;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return infos;
    }

    @Override
    public void executeAsATransaction(Runnable actions) {
        boolean transactionStarted = false;
        try {
            myDatabase.beginTransaction();
            transactionStarted = true;
        } catch (Throwable t) {
        }
        try {
            actions.run();
            if (transactionStarted) {
                myDatabase.setTransactionSuccessful();
            }
        } finally {
            if (transactionStarted) {
                myDatabase.endTransaction();
            }
        }
    }

    @Override
    public void removeFileInfo(long fileId) {
        if (fileId == -1) {
            return;
        }
        if (myRemoveFileInfoStatement == null) {
            myRemoveFileInfoStatement = myDatabase.compileStatement(
                    "DELETE FROM Files WHERE file_id = ?"
            );
        }
        myRemoveFileInfoStatement.bindLong(1, fileId);
        myRemoveFileInfoStatement.execute();
    }

    @Override
    public void saveFileInfo(FileInfo fileInfo) {
        final long id = fileInfo.Id;
        SQLiteStatement statement;
        if (id == -1) {
            if (myInsertFileInfoStatement == null) {
                myInsertFileInfoStatement = myDatabase.compileStatement(
                        "INSERT OR IGNORE INTO Files (name,parent_id,size) VALUES (?,?,?)"
                );
            }
            statement = myInsertFileInfoStatement;
        } else {
            if (myUpdateFileInfoStatement == null) {
                myUpdateFileInfoStatement = myDatabase.compileStatement(
                        "UPDATE Files SET name = ?, parent_id = ?, size = ? WHERE file_id = ?"
                );
            }
            statement = myUpdateFileInfoStatement;
        }
        statement.bindString(1, fileInfo.Name);
        final FileInfo parent = fileInfo.Parent;
        if (parent != null) {
            statement.bindLong(2, parent.Id);
        } else {
            statement.bindNull(2);
        }
        final long size = fileInfo.FileSize;
        if (size != -1) {
            statement.bindLong(3, size);
        } else {
            statement.bindNull(3);
        }
        if (id == -1) {
            fileInfo.Id = statement.executeInsert();
        } else {
            statement.bindLong(4, id);
            statement.execute();
        }
    }

    @Override
    public ReadBook loadBookByFile(long fileId, ZLFile file) {
        if (fileId == -1) {
            return null;
        }
        ReadBook book = null;
        final Cursor cursor = myDatabase.rawQuery("SELECT book_id,title,encoding,language, file_mode FROM Books WHERE file_id = " + fileId, null);
        if (cursor.moveToNext()) {
            book = createBook(cursor.getLong(0),
                    file,
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4)
            );
        }
        cursor.close();
        return book;
    }


    public ReadBook createBook(long id, ZLFile file, String title, String encoding, String language, String fileMode) {
//        if (fileMode != null && fileMode.startsWith(FileType17KTxtNovel.FileMode) && file instanceof ZL17KPlainTxtFile) {
//            return new M17kPlainTxtBook(id, (ZL17KPlainTxtFile) file, ((ZL17KPlainTxtFile) file).getBookName(), encoding, language);
//        } else
        if (fileMode != null && fileMode.startsWith(FileTypeTxt.FileMode) && file instanceof ZLTxtFile) {
            return new MTxtBook(id, (ZLTxtFile) file, ((ZLTxtFile) file).getBookName(), encoding, language);
        }
        return (file != null) ? new ReadBook(id, file, title, encoding, language) : null;
    }


    public FileInfo createFileInfo(long id, String name, FileInfo parent) {
        return new FileInfo(name, parent, id);
    }
}
